#!/usr/bin/python3
# -*- coding: utf-8 -*-
#======================================#
# @Author   : duanqizhong
# @time     : 2022-8-16 11:27
# @file     : check_and_notify
# @Software : PyCharm
#======================================#


import sys
import time
import datetime
from db.mysql import exec_sql,query_time
from conf.auto_point_dbs import ParsePointDbs
from logger.log import Log
from pyconf.setting import anemometer,slow_sql_log,dbConf,backup,mescanal,slow_sql_api
from dingding.send import send_link

datetime_format="%Y-%m-%d %H:%M:%S"
date_format="%Y-%m-%d 00:00:00"
date_format2="%Y%m"
datetime_format1="%Y%m%d%H%M%S"

date=datetime.datetime.now().strftime(date_format2)

logging=Log("{log_path}-{date}".format(log_path=slow_sql_log,date=date),"SLOW_SQL_CHECK_LOG").get_file_hander()

def review_sql(env,bs,sql,database,time_out=3):
    #env_section=dbConf[env].copy()
    #env_section.update(mescanal)

    #spend_time=query_time(env_section,sql,database)
    spend_time=query_time(backup,sql,database)
    if spend_time is None:
        logging.warning("please conn stage_bak check {database} \n {sql}".format(database=database,sql=sql))
        return {"status":True,"code":2}
    logging.info("{env} {bs} {db} query time is {times}s".format(env=env,bs=bs,db=database,times=spend_time))
    if float(spend_time)>time_out:
        return {"status":True,"code":1}
    return {"status":False,"code":0}

def get_sql_and_review(env):
    """
    only check business db where alarm is True
    """
    infos=get_last_day_slow_sql(env)
    if infos is None or not infos:
        logging.info("{env} not find slow sql".format(env=env))
        return
    business=dbConf[env]["alarms"]
    business_infos={}
    bs_parse=ParsePointDbs().get_parse()
    counts={}
    errors_business=[]
    for bs in business:
        business_info=bs_parse.get_section_info(bs)
        if business_info is None or not business_info:
           logging.error("不存在 business={bs}, 请核查！".format(bs=bs))
           errors_business.append(bs)
           continue
        business_infos[bs]=business_info
        counts[bs]={}
        counts[bs]["checksum"]=[]
        counts[bs]["status"]=False
        counts[bs]["env"]=env
        counts[bs]["manual"]={}
        counts[bs]["manual"]["checksum"]=[]
        counts[bs]["manual"]["status"]=False

    for record in infos:
        """record: checksum hostname db sample"""
        db=record[2].strip()
        for key,value in business_infos.items():
            if key in errors_business:
                continue
            counts[key]["dingToken"]=value["dingtoken"]
            if db in value['dbs'].split(';'):
               logging.info("{env} {bs} {db} in alarm business list".format(env=env,bs=key,db=record[2]))
               review_status=review_sql(env,key,record[3],record[2])
               if review_status["status"] and review_status["code"]==1:
                  # counts or send ding ding
                  logging.warning("found slow sql, please check {env} {bs} {database} checksum={checksum}".format(env=env,bs=key,database=record[2],checksum=record[0]))
                  db_checksum={"database":db,"checksum":record[0]}
                  counts[key]["checksum"].append(db_checksum)
                  counts[key]["status"]=True
               elif review_status["status"] and review_status["code"]==2:
                   logging.warning("found slow sql, please check {env} {bs} {database} checksum={checksum}".format(env=env,bs=key,database=record[2],checksum=record[0]))
                   db_checksum={"database":db,"checksum":record[0]}
                   counts[key]["manual"]["checksum"].append(db_checksum)
                   counts[key]["manual"]["status"]=True
               else:
                   logging.info("{env} {bs} {database} not fount slow sql".format(env=env,bs=key,database=record[2]))
               break
            else:
               pass
        else:
            if bs not in errors_business:
               logging.warning("{env} {bs} {db} not in alarm business list".format(env=env,bs=key,db=record[2]))
    return counts

def get_last_day_slow_sql(env):
    time_now=datetime.datetime.now()
    offset=datetime.timedelta(days=-1)
    start_time=(time_now+offset).strftime(date_format)
    end_time=time_now.strftime(date_format)
    sql="""select distinct gqr.checksum,gqrh.hostname_max,gqrh.db_max,gqr.sample from global_query_review as gqr
               left join global_query_review_history as gqrh on gqrh.checksum=gqr.checksum and gqrh.sample=gqr.sample
               where hostname_max like '{env}%'
               and last_seen >= '{start_time}'
               and last_seen < '{end_time}'""".format(env=env,start_time=start_time,end_time=end_time)
    infos=exec_sql(anemometer,sql,anemometer["database"])
    if infos is None or not infos:
        return []
    logging.info("总共发现 {number} 条慢查！".format(number=len(infos)))
    return infos

def send_dingding(review_status):
    if not isinstance(review_status,dict):
        return
    for bs,bs_info in review_status.items():
        if bs_info["status"]:
            for db_cksum in bs_info["checksum"]:
                text="{env}环境 {database} 出现慢查checksum={checksum}，点击查看详情！".format(env=bs_info["env"],database=db_cksum["database"],checksum=db_cksum["checksum"])
                title="慢SQL信息通知(如有疑问，请用vipkid用户在 10.32.32.44:3309 库校验)"
                messageUrl="{slow_api}{checksum}".format(slow_api=slow_sql_api,checksum=db_cksum["checksum"])
                send_link(title,text,messageUrl,bs_info["dingToken"])
                time.sleep(3)

        if bs_info["manual"]["status"]:
            for db_cksum in bs_info["manual"]["checksum"]:
                text="{env}环境 {database} 出现无法自动校验的慢查checksum={checksum}，请手动检查，点击查看详情！".format(env=bs_info["env"],database=db_cksum["database"],checksum=db_cksum["checksum"])
                title="慢SQL手动核查提醒(如有疑问，请用vipkid用户在 10.32.32.44:3309 库校验)"
                messageUrl="{slow_api}{checksum}".format(slow_api=slow_sql_api,checksum=db_cksum["checksum"])
                send_link(title,text,messageUrl,bs_info["dingToken"])
                time.sleep(3)

def review_and_alarm(env=None):
    review_sql_status=get_sql_and_review(env)
    send_dingding(review_sql_status)


def run(env=None):
    if env is None:
        for env in dbConf.keys():
            if dbConf[env]["slow_log"]:
                logging.info("start check {env} sql and send alarm!".format(env=env))
                review_and_alarm(env)
            else:
                logging.warning("{env} not open slow_query log".format(env=env))
    else:
        if env in dbConf.keys() and dbConf[env]["slow_log"]:
            logging.info("start check {env} sql and send alarm!".format(env=env))
            review_and_alarm(env)

if __name__=="__main__":
    if sys.argv[-1]!="yes" or len(sys.argv) not in (2,3):
        print("参数格式：yes or env yes")
        sys.exit()
    if len(sys.argv)==2:
       run()
    else:
       run(sys.argv[1])